USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorTickboxesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorTickboxesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorTickboxesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	
	
	SELECT  Indicators.IndicatorId,
	        Companies.CompanyId, 
			IndicatorTemplates.IndicatorId AS IndicatorTemplateId,
			Tickboxes.Code AS TickboxCode,
			Tickboxes.Text AS TickboxText,
			IndicatorTickboxes.IsChecked

	FROM	dbo.Companies AS Companies 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId
    INNER JOIN dbo.IndicatorsInCompanies ON Companies.CompanyId = IndicatorsInCompanies.CompanyId 
    INNER JOIN dbo.Indicators AS Indicators ON IndicatorsInCompanies.IndicatorId = Indicators.IndicatorId AND Companies.WorkingVersion - 1 = Indicators.Version 
    INNER JOIN dbo.Indicators AS IndicatorTemplates ON Indicators.Number = IndicatorTemplates.Number 
    INNER JOIN dbo.IndicatorTickboxes AS IndicatorTickboxes ON Indicators.IndicatorId = IndicatorTickboxes.IndicatorId
	INNER JOIN dbo.Tickboxes AS Tickboxes ON IndicatorTickboxes.TickboxId = Tickboxes.Id

	WHERE	(Indicators.Enabled = 1) 
	AND 	(IndicatorTemplates.Template = 1) 
	AND 	(Indicators.Template = 0) 
	AND 	(Companies.WorkingVersion > 1002) 
    AND     (Companies.IsParked = 0) 
    AND     (Indicators.TickboxesEnabled = 1)
END
GO


